{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "e12ef948",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>编号</th>\n",
       "      <th>日期</th>\n",
       "      <th>姓名</th>\n",
       "      <th>性别</th>\n",
       "      <th>年龄</th>\n",
       "      <th>工资</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100001</td>\n",
       "      <td>2021-11-08</td>\n",
       "      <td>赵佳</td>\n",
       "      <td>女</td>\n",
       "      <td>25</td>\n",
       "      <td>5869.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100012</td>\n",
       "      <td>2021-11-09</td>\n",
       "      <td>张可</td>\n",
       "      <td>男</td>\n",
       "      <td>28</td>\n",
       "      <td>7256.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100003</td>\n",
       "      <td>2021-11-10</td>\n",
       "      <td>周远</td>\n",
       "      <td>女</td>\n",
       "      <td>21</td>\n",
       "      <td>6895.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100004</td>\n",
       "      <td>2021-11-11</td>\n",
       "      <td>徐南</td>\n",
       "      <td>男</td>\n",
       "      <td>30</td>\n",
       "      <td>7289.72</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       编号         日期  姓名 性别  年龄       工资\n",
       "0  100001 2021-11-08  赵佳  女  25  5869.32\n",
       "1  100012 2021-11-09  张可  男  28  7256.34\n",
       "2  100003 2021-11-10  周远  女  21  6895.89\n",
       "3  100004 2021-11-11  徐南  男  30  7289.72"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "data = {  \"编号\":[100001,100012,100003,100004],\n",
    "          \"日期\":pd.date_range('20211108', periods=4),\n",
    "          \"姓名\":[\"赵佳\",\"张可\",\"周远\",\"徐南\"],\n",
    "          \"性别\":['女','男','女','男'],\n",
    "          \"年龄\":[25,28,21,30],\n",
    "          \"工资\":[5869.32,7256.34,6895.89,7289.72]\n",
    "       }\n",
    "mydf1 = pd.DataFrame(data) \n",
    "display(mydf1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "68439604",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>编号</th>\n",
       "      <th>日期</th>\n",
       "      <th>姓名</th>\n",
       "      <th>性别</th>\n",
       "      <th>年龄</th>\n",
       "      <th>工资</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100004</td>\n",
       "      <td>2021-11-11</td>\n",
       "      <td>徐南</td>\n",
       "      <td>男</td>\n",
       "      <td>30</td>\n",
       "      <td>7289.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100003</td>\n",
       "      <td>2021-11-10</td>\n",
       "      <td>周远</td>\n",
       "      <td>女</td>\n",
       "      <td>21</td>\n",
       "      <td>6895.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100012</td>\n",
       "      <td>2021-11-09</td>\n",
       "      <td>张可</td>\n",
       "      <td>男</td>\n",
       "      <td>28</td>\n",
       "      <td>7256.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100001</td>\n",
       "      <td>2021-11-08</td>\n",
       "      <td>赵佳</td>\n",
       "      <td>女</td>\n",
       "      <td>25</td>\n",
       "      <td>5869.32</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       编号         日期  姓名 性别  年龄       工资\n",
       "3  100004 2021-11-11  徐南  男  30  7289.72\n",
       "2  100003 2021-11-10  周远  女  21  6895.89\n",
       "1  100012 2021-11-09  张可  男  28  7256.34\n",
       "0  100001 2021-11-08  赵佳  女  25  5869.32"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "mydf1.set_index(['编号'])\n",
    "display(mydf1.sort_index(ascending=False))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "b0ca1384",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>编号</th>\n",
       "      <th>日期</th>\n",
       "      <th>姓名</th>\n",
       "      <th>性别</th>\n",
       "      <th>年龄</th>\n",
       "      <th>工资</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100004</td>\n",
       "      <td>2021-11-11</td>\n",
       "      <td>徐南</td>\n",
       "      <td>男</td>\n",
       "      <td>30</td>\n",
       "      <td>7289.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100012</td>\n",
       "      <td>2021-11-09</td>\n",
       "      <td>张可</td>\n",
       "      <td>男</td>\n",
       "      <td>28</td>\n",
       "      <td>7256.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100003</td>\n",
       "      <td>2021-11-10</td>\n",
       "      <td>周远</td>\n",
       "      <td>女</td>\n",
       "      <td>21</td>\n",
       "      <td>6895.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100001</td>\n",
       "      <td>2021-11-08</td>\n",
       "      <td>赵佳</td>\n",
       "      <td>女</td>\n",
       "      <td>25</td>\n",
       "      <td>5869.32</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       编号         日期  姓名 性别  年龄       工资\n",
       "3  100004 2021-11-11  徐南  男  30  7289.72\n",
       "1  100012 2021-11-09  张可  男  28  7256.34\n",
       "2  100003 2021-11-10  周远  女  21  6895.89\n",
       "0  100001 2021-11-08  赵佳  女  25  5869.32"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(mydf1.sort_values(by='工资',ascending=False))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "65616030",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "编号     100001\n",
      "姓名         赵佳\n",
      "性别          女\n",
      "年龄         25\n",
      "工资    5869.32\n",
      "Name: 0, dtype: object\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "data = {  \"编号\":[100001,100012,100003,100004,100005,100006,100007, 10008],\n",
    "         \"姓名\":[\"赵佳\",\"张可\",\"周远\",\"徐南\",\"赵杰\",\"王永亮\",\"李丽\",\"曲波\",],\n",
    "          \"性别\":['女','男','女','男','男','男','女','男'],\n",
    "          \"年龄\":[25,32,21,35,22,25,31,36],\n",
    "          \"工资\":[5869.32,7256.34,6895.89,7289.72,4895.21,6512.89, 8865.38,8281.45]\n",
    "       }\n",
    "mydf1 = pd.DataFrame(data) \n",
    "print(mydf1.loc[0])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "4c1936e8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>编号</th>\n",
       "      <th>姓名</th>\n",
       "      <th>性别</th>\n",
       "      <th>年龄</th>\n",
       "      <th>工资</th>\n",
       "      <th>工资分组</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100001</td>\n",
       "      <td>赵佳</td>\n",
       "      <td>女</td>\n",
       "      <td>25</td>\n",
       "      <td>5869.32</td>\n",
       "      <td>低</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100012</td>\n",
       "      <td>张可</td>\n",
       "      <td>男</td>\n",
       "      <td>32</td>\n",
       "      <td>7256.34</td>\n",
       "      <td>高</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100003</td>\n",
       "      <td>周远</td>\n",
       "      <td>女</td>\n",
       "      <td>21</td>\n",
       "      <td>6895.89</td>\n",
       "      <td>低</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100004</td>\n",
       "      <td>徐南</td>\n",
       "      <td>男</td>\n",
       "      <td>35</td>\n",
       "      <td>7289.72</td>\n",
       "      <td>高</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100005</td>\n",
       "      <td>赵杰</td>\n",
       "      <td>男</td>\n",
       "      <td>22</td>\n",
       "      <td>4895.21</td>\n",
       "      <td>低</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>100006</td>\n",
       "      <td>王永亮</td>\n",
       "      <td>男</td>\n",
       "      <td>25</td>\n",
       "      <td>6512.89</td>\n",
       "      <td>低</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>100007</td>\n",
       "      <td>李丽</td>\n",
       "      <td>女</td>\n",
       "      <td>31</td>\n",
       "      <td>8865.38</td>\n",
       "      <td>高</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>10008</td>\n",
       "      <td>曲波</td>\n",
       "      <td>男</td>\n",
       "      <td>36</td>\n",
       "      <td>8281.45</td>\n",
       "      <td>高</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       编号   姓名 性别  年龄       工资 工资分组\n",
       "0  100001   赵佳  女  25  5869.32    低\n",
       "1  100012   张可  男  32  7256.34    高\n",
       "2  100003   周远  女  21  6895.89    低\n",
       "3  100004   徐南  男  35  7289.72    高\n",
       "4  100005   赵杰  男  22  4895.21    低\n",
       "5  100006  王永亮  男  25  6512.89    低\n",
       "6  100007   李丽  女  31  8865.38    高\n",
       "7   10008   曲波  男  36  8281.45    高"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import numpy as np\n",
    "mydf1['工资分组'] = np.where(mydf1['工资'] >7000,'高','低')\n",
    "display(mydf1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "3f507cd8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>编号</th>\n",
       "      <th>姓名</th>\n",
       "      <th>性别</th>\n",
       "      <th>年龄</th>\n",
       "      <th>工资</th>\n",
       "      <th>工资分组</th>\n",
       "      <th>年龄分组</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100001</td>\n",
       "      <td>赵佳</td>\n",
       "      <td>女</td>\n",
       "      <td>25</td>\n",
       "      <td>5869.32</td>\n",
       "      <td>低</td>\n",
       "      <td>新职工</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100012</td>\n",
       "      <td>张可</td>\n",
       "      <td>男</td>\n",
       "      <td>32</td>\n",
       "      <td>7256.34</td>\n",
       "      <td>高</td>\n",
       "      <td>骨干职工</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100003</td>\n",
       "      <td>周远</td>\n",
       "      <td>女</td>\n",
       "      <td>21</td>\n",
       "      <td>6895.89</td>\n",
       "      <td>低</td>\n",
       "      <td>新职工</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100004</td>\n",
       "      <td>徐南</td>\n",
       "      <td>男</td>\n",
       "      <td>35</td>\n",
       "      <td>7289.72</td>\n",
       "      <td>高</td>\n",
       "      <td>骨干职工</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100005</td>\n",
       "      <td>赵杰</td>\n",
       "      <td>男</td>\n",
       "      <td>22</td>\n",
       "      <td>4895.21</td>\n",
       "      <td>低</td>\n",
       "      <td>新职工</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>100006</td>\n",
       "      <td>王永亮</td>\n",
       "      <td>男</td>\n",
       "      <td>25</td>\n",
       "      <td>6512.89</td>\n",
       "      <td>低</td>\n",
       "      <td>新职工</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>100007</td>\n",
       "      <td>李丽</td>\n",
       "      <td>女</td>\n",
       "      <td>31</td>\n",
       "      <td>8865.38</td>\n",
       "      <td>高</td>\n",
       "      <td>骨干职工</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>10008</td>\n",
       "      <td>曲波</td>\n",
       "      <td>男</td>\n",
       "      <td>36</td>\n",
       "      <td>8281.45</td>\n",
       "      <td>高</td>\n",
       "      <td>骨干职工</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       编号   姓名 性别  年龄       工资 工资分组  年龄分组\n",
       "0  100001   赵佳  女  25  5869.32    低   新职工\n",
       "1  100012   张可  男  32  7256.34    高  骨干职工\n",
       "2  100003   周远  女  21  6895.89    低   新职工\n",
       "3  100004   徐南  男  35  7289.72    高  骨干职工\n",
       "4  100005   赵杰  男  22  4895.21    低   新职工\n",
       "5  100006  王永亮  男  25  6512.89    低   新职工\n",
       "6  100007   李丽  女  31  8865.38    高  骨干职工\n",
       "7   10008   曲波  男  36  8281.45    高  骨干职工"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import numpy as np\n",
    "mydf1['年龄分组'] = np.where(mydf1['年龄'] <=30,'新职工','骨干职工')\n",
    "display(mydf1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "f151d952",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>电器产品</th>\n",
       "      <th>业务员</th>\n",
       "      <th>时间</th>\n",
       "      <th>城市</th>\n",
       "      <th>数量</th>\n",
       "      <th>单价</th>\n",
       "      <th>销售额</th>\n",
       "      <th>标记1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MP3</td>\n",
       "      <td>李可</td>\n",
       "      <td>春季</td>\n",
       "      <td>上海</td>\n",
       "      <td>541</td>\n",
       "      <td>125</td>\n",
       "      <td>67625</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MP3</td>\n",
       "      <td>李可</td>\n",
       "      <td>秋季</td>\n",
       "      <td>青岛</td>\n",
       "      <td>674</td>\n",
       "      <td>125</td>\n",
       "      <td>84250</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MP3</td>\n",
       "      <td>李亮</td>\n",
       "      <td>春季</td>\n",
       "      <td>上海</td>\n",
       "      <td>720</td>\n",
       "      <td>125</td>\n",
       "      <td>90000</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MP3</td>\n",
       "      <td>李亮</td>\n",
       "      <td>夏季</td>\n",
       "      <td>上海</td>\n",
       "      <td>641</td>\n",
       "      <td>125</td>\n",
       "      <td>80125</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MP3</td>\n",
       "      <td>张平</td>\n",
       "      <td>春季</td>\n",
       "      <td>上海</td>\n",
       "      <td>721</td>\n",
       "      <td>125</td>\n",
       "      <td>90125</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>MP3</td>\n",
       "      <td>张平</td>\n",
       "      <td>夏季</td>\n",
       "      <td>青岛</td>\n",
       "      <td>384</td>\n",
       "      <td>125</td>\n",
       "      <td>48000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>MP3</td>\n",
       "      <td>周顺利</td>\n",
       "      <td>夏季</td>\n",
       "      <td>上海</td>\n",
       "      <td>354</td>\n",
       "      <td>125</td>\n",
       "      <td>44250</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>MP3</td>\n",
       "      <td>周顺利</td>\n",
       "      <td>秋季</td>\n",
       "      <td>青岛</td>\n",
       "      <td>841</td>\n",
       "      <td>125</td>\n",
       "      <td>105125</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  电器产品  业务员  时间  城市   数量   单价     销售额  标记1\n",
       "0  MP3   李可  春季  上海  541  125   67625  1.0\n",
       "1  MP3   李可  秋季  青岛  674  125   84250  NaN\n",
       "2  MP3   李亮  春季  上海  720  125   90000  1.0\n",
       "3  MP3   李亮  夏季  上海  641  125   80125  NaN\n",
       "4  MP3   张平  春季  上海  721  125   90125  1.0\n",
       "5  MP3   张平  夏季  青岛  384  125   48000  NaN\n",
       "6  MP3  周顺利  夏季  上海  354  125   44250  NaN\n",
       "7  MP3  周顺利  秋季  青岛  841  125  105125  NaN"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "mydf1 = pd.read_excel('myexcel1.xls',sheet_name=1)\n",
    "mydf1.loc[(mydf1['时间']== '春季')&(mydf1['数量']>500),'标记1'] = 1.0\n",
    "display(mydf1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "7bd7423a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     好\n",
       "1     好\n",
       "2     好\n",
       "3    不好\n",
       "4     好\n",
       "5    不好\n",
       "6    不好\n",
       "7     好\n",
       "Name: 标记2, dtype: object"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mydf1.loc[((mydf1['时间'] == '春季')|(mydf1['时间']=='秋季'))&(mydf1['销售额'] >=60000),'标记2'] = '好'\n",
    "mydf1 = mydf1['标记2'].fillna('不好')\n",
    "mydf1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "19066600",
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'mydf1' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[1], line 2\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[38;5;28;01mimport\u001b[39;00m \u001b[38;5;21;01mnumpy\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m \u001b[38;5;21;01mnp\u001b[39;00m\n\u001b[1;32m----> 2\u001b[0m mydf1[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m标记3\u001b[39m\u001b[38;5;124m'\u001b[39m] \u001b[38;5;241m=\u001b[39m np\u001b[38;5;241m.\u001b[39mwhere((\u001b[43mmydf1\u001b[49m[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m数量\u001b[39m\u001b[38;5;124m'\u001b[39m] \u001b[38;5;241m<\u001b[39m\u001b[38;5;241m=\u001b[39m\u001b[38;5;241m700\u001b[39m)\u001b[38;5;241m&\u001b[39m(mydf1[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m城市\u001b[39m\u001b[38;5;124m'\u001b[39m]\u001b[38;5;241m==\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m青岛\u001b[39m\u001b[38;5;124m'\u001b[39m),\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mbad\u001b[39m\u001b[38;5;124m'\u001b[39m,\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mgood\u001b[39m\u001b[38;5;124m'\u001b[39m)\n\u001b[0;32m      3\u001b[0m display(mydf1)\n",
      "\u001b[1;31mNameError\u001b[0m: name 'mydf1' is not defined"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "mydf1['标记3'] = np.where((mydf1['数量'] <=700)&(mydf1['城市']=='青岛'),'bad','good')\n",
    "display(mydf1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "675621d4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>类型</th>\n",
       "      <th>品牌</th>\n",
       "      <th>价格</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>编号</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>100001</th>\n",
       "      <td>2021-11-08</td>\n",
       "      <td>computer</td>\n",
       "      <td>Mac-Dell-Lenovo</td>\n",
       "      <td>5869.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100012</th>\n",
       "      <td>2021-11-09</td>\n",
       "      <td>phone</td>\n",
       "      <td>Mac-XiaoMi-HuaWei</td>\n",
       "      <td>7256.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100003</th>\n",
       "      <td>2021-11-10</td>\n",
       "      <td>pad</td>\n",
       "      <td>Mac-HuaWei</td>\n",
       "      <td>3895.89</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               日期        类型                 品牌       价格\n",
       "编号                                                     \n",
       "100001 2021-11-08  computer    Mac-Dell-Lenovo  5869.32\n",
       "100012 2021-11-09     phone  Mac-XiaoMi-HuaWei  7256.34\n",
       "100003 2021-11-10       pad         Mac-HuaWei  3895.89"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#列的拆分\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "data = {  \"编号\":[100001,100012,100003],\n",
    "          \"日期\":pd.date_range('20211108', periods=3),\n",
    "          \"类型\":[\"computer\",\"phone\",\"pad\"],\n",
    "          \"品牌\":['Mac-Dell-Lenovo','Mac-XiaoMi-HuaWei','Mac-HuaWei'],\n",
    "          \"价格\":[5869.32,7256.34,3895.89]\n",
    "       }\n",
    "mydf1 = pd.DataFrame(data) \n",
    "mydf1.set_index(['编号'],inplace=True)\n",
    "display(mydf1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "5c74a080",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>类型</th>\n",
       "      <th>品牌</th>\n",
       "      <th>价格</th>\n",
       "      <th>品牌-1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>编号</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>100001</th>\n",
       "      <td>2021-11-08</td>\n",
       "      <td>computer</td>\n",
       "      <td>Mac-Dell-Lenovo</td>\n",
       "      <td>5869.32</td>\n",
       "      <td>Mac</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100012</th>\n",
       "      <td>2021-11-09</td>\n",
       "      <td>phone</td>\n",
       "      <td>Mac-XiaoMi-HuaWei</td>\n",
       "      <td>7256.34</td>\n",
       "      <td>Mac</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100003</th>\n",
       "      <td>2021-11-10</td>\n",
       "      <td>pad</td>\n",
       "      <td>Mac-HuaWei</td>\n",
       "      <td>3895.89</td>\n",
       "      <td>Mac</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               日期        类型                 品牌       价格 品牌-1\n",
       "编号                                                          \n",
       "100001 2021-11-08  computer    Mac-Dell-Lenovo  5869.32  Mac\n",
       "100012 2021-11-09     phone  Mac-XiaoMi-HuaWei  7256.34  Mac\n",
       "100003 2021-11-10       pad         Mac-HuaWei  3895.89  Mac"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "mydf1['品牌-1'] = mydf1.品牌.astype('str').str[0:3]\n",
    "display(mydf1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "091908ca",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>类型</th>\n",
       "      <th>品牌</th>\n",
       "      <th>价格</th>\n",
       "      <th>品牌-1</th>\n",
       "      <th>品牌2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>编号</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>100001</th>\n",
       "      <td>2021-11-08</td>\n",
       "      <td>computer</td>\n",
       "      <td>Mac-Dell-Lenovo</td>\n",
       "      <td>5869.32</td>\n",
       "      <td>Mac</td>\n",
       "      <td>Dell</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100012</th>\n",
       "      <td>2021-11-09</td>\n",
       "      <td>phone</td>\n",
       "      <td>Mac-XiaoMi-HuaWei</td>\n",
       "      <td>7256.34</td>\n",
       "      <td>Mac</td>\n",
       "      <td>XiaoMi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100003</th>\n",
       "      <td>2021-11-10</td>\n",
       "      <td>pad</td>\n",
       "      <td>Mac-HuaWei</td>\n",
       "      <td>3895.89</td>\n",
       "      <td>Mac</td>\n",
       "      <td>HuaWei</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               日期        类型                 品牌       价格 品牌-1     品牌2\n",
       "编号                                                                  \n",
       "100001 2021-11-08  computer    Mac-Dell-Lenovo  5869.32  Mac    Dell\n",
       "100012 2021-11-09     phone  Mac-XiaoMi-HuaWei  7256.34  Mac  XiaoMi\n",
       "100003 2021-11-10       pad         Mac-HuaWei  3895.89  Mac  HuaWei"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "mydf1['品牌2'] = mydf1.品牌.apply(lambda x:x.split('-')[1])\n",
    "display(mydf1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "dc069c38",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>类型</th>\n",
       "      <th>品牌</th>\n",
       "      <th>价格</th>\n",
       "      <th>品牌3</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>编号</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>100001</th>\n",
       "      <td>2021-11-08</td>\n",
       "      <td>computer</td>\n",
       "      <td>Mac-Dell-Lenovo</td>\n",
       "      <td>5869.32</td>\n",
       "      <td>Lenovo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100012</th>\n",
       "      <td>2021-11-09</td>\n",
       "      <td>phone</td>\n",
       "      <td>Mac-XiaoMi-HuaWei</td>\n",
       "      <td>7256.34</td>\n",
       "      <td>HuaWei</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100003</th>\n",
       "      <td>2021-11-10</td>\n",
       "      <td>pad</td>\n",
       "      <td>Mac-HuaWei</td>\n",
       "      <td>3895.89</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               日期        类型                 品牌       价格     品牌3\n",
       "编号                                                             \n",
       "100001 2021-11-08  computer    Mac-Dell-Lenovo  5869.32  Lenovo\n",
       "100012 2021-11-09     phone  Mac-XiaoMi-HuaWei  7256.34  HuaWei\n",
       "100003 2021-11-10       pad         Mac-HuaWei  3895.89     NaN"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mydf1['品牌3'] = mydf1['品牌'].apply(lambda x:x.split('-')[2] if x.count('-')>=2 else np.nan)\n",
    "mydf1"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
